﻿CREATE PROCEDURE [dbo].[SalesSummaryReport]
	@BeginDate datetime, 
	@EndDate datetime,
	@ProjectId int
WITH RECOMPILE 
AS
BEGIN
	SET @EndDate = DateAdd(day, 1, @EndDate);
	WITH 
	[EntranceView]
	AS
	(
		SELECT	[Id],
						[BuildingId]
		FROM		[dbo].[Entrance]
		WHERE	[IsDeleted] = 0
	),
	[FloorView]
	AS
	(
		SELECT	[Id],
						[BuildingId]
		FROM		[dbo].[Floor]
		WHERE	[IsDeleted] = 0
	),
	[BuildingView]
	AS
	(
		SELECT	[Id],
						[ProjectId]
		FROM		[dbo].[Building]
		WHERE	[IsDeleted] = 0
	),
	[ProjectView]
	AS
	(
		SELECT	[Id], 
						[Name]
		FROM		[dbo].[Project]
		WHERE	[IsDeleted] = 0 AND [Id] = @ProjectId
	),
	[RoomView]
	AS
	(
		SELECT	R.[Id],
						F.[Id] AS [FloorId],
						E.[Id] AS [EntranceId],
						B.[Id] AS [BuildingId],
						P.[Id] AS [ProjectId], 
						R.[SaleArea], 
						R.[ActiveAgreementId]
		FROM		[dbo].[Room] AS R 
			INNER JOIN [EntranceView] AS E ON R.[EntranceId] = E.[Id]
			INNER JOIN [FloorView] AS F ON R.[FloorId] = F.[Id]
			INNER JOIN [BuildingView] AS B ON E.[BuildingId] = B.[Id]
			INNER JOIN [ProjectView] AS P ON B.[ProjectId] = P.[Id]
		WHERE R.[IsDeleted] = 0
	),
	[ActiveAgreementView]
	AS
	(
		SELECT	AG.[Id],
						AG.[RoomId],
						AG.[SalesEmployeeId], 
						AG.[CustomerId], 
						R.[SaleArea]
		FROM		[dbo].[Agreement] AS AG
			INNER JOIN [RoomView] AS R ON AG.[RoomId] = R.[Id] AND AG.[Id] = R.[ActiveAgreementId] 
		WHERE	AG.[IsDeleted] = 0
	),
	[AgreementView]
	AS
	(
		SELECT	AG.[Id],
						AG.[RoomId],
						AG.[SalesEmployeeId], 
						AG.[CustomerId], 
						R.[SaleArea]
		FROM		[dbo].[Agreement] AS AG
			INNER JOIN [RoomView] AS R ON AG.[RoomId] = R.[Id]
		WHERE	AG.[IsDeleted] = 0
	),
	[SmallOrderView]
	AS
	(
		SELECT	SO.[Id], 
						SO.[AgreementId]
		FROM		[dbo].[SmallOrder] AS SO
			INNER JOIN [AgreementView] AS AG ON SO.[AgreementId] = AG.[Id]
		WHERE	SO.[IsDeleted] = 0 AND SO.[SignatureDate] >= @BeginDate AND SO.[SignatureDate] < @EndDate
	),
	[LargeOrderView]
	AS
	(
		SELECT	LO.[Id], 
						LO.[AgreementId]
		FROM		[dbo].[LargeOrder] AS LO
			INNER JOIN [AgreementView] AS AG ON LO.[AgreementId] = AG.[Id]
		WHERE	LO.[IsDeleted] = 0 AND LO.[SignatureDate] >= @BeginDate AND LO.[SignatureDate] < @EndDate
	),
	[ContractReceiptView]
	AS
	(
		SELECT	CR.[Id],
						CR.[ContractId],
						CR.[ReceiptTypeId],
						CR.[ScheduledDate], 
						CR.[ReceivableAmount], 
						ISNULL(CRR.[ReceivedAmount], 0) AS [ReceivedAmount]
		FROM		[dbo].[ContractReceipt] AS CR
			LEFT OUTER JOIN
			(
				SELECT	[ContractReceiptId],
								SUM([ReceivedAmount]) AS [ReceivedAmount]
				FROM		[dbo].[ContractReceivedReceipt]
				WHERE	[IsDeleted] = 0 AND [ReceivedDate] >= @BeginDate AND [ReceivedDate] < @EndDate
				GROUP BY
								[ContractReceiptId]
			) AS CRR 	ON CR.[Id] = CRR.[ContractReceiptId]
		WHERE	CR.[IsDeleted] = 0
	),
	[ContractView]
	AS
	(
		SELECT	C.[Id], 
						C.[AgreementId],
						AG.[SaleArea], 
						C.[ReducedTotalPrice] AS [TotalAmount],
						CASE WHEN ISNULL(CR.[InstalmentReceivableAmount], 0) < ISNULL(CR.[InstalmentReceivedAmount], 0) THEN 0 ELSE ISNULL(CR.[InstalmentReceivableAmount], 0) - ISNULL(CR.[InstalmentReceivedAmount], 0) END AS [DiffInstalmentReceivableAmount], 
						CASE WHEN ISNULL(CR.[MortgageReceivableAmount], 0) < ISNULL(CR.[MortgageReceivedAmount], 0) THEN 0 ELSE ISNULL(CR.[MortgageReceivableAmount], 0) - ISNULL(CR.[MortgageReceivedAmount], 0) END AS [DiffMortgageReceivedAmount], 
						ISNULL(CR.[ReceivedAmount], 0) AS [ReceivedAmount]
		FROM		[dbo].[Contract] AS C
			INNER JOIN [AgreementView] AS AG ON C.[AgreementId] = AG.[Id]
			LEFT OUTER JOIN
			(
				SELECT	CR.[ContractId],
								(SELECT SUM([ReceivableAmount]) FROM [ContractReceiptView] WHERE [ContractId] = CR.[ContractId] AND [ScheduledDate] >= @BeginDate AND [ScheduledDate] < @EndDate AND [ReceiptTypeId] IN (1,2)) AS [InstalmentReceivableAmount], 
								(SELECT SUM([ReceivableAmount]) FROM [ContractReceiptView] WHERE [ContractId] = CR.[ContractId] AND [ScheduledDate] >= @BeginDate AND [ScheduledDate] < @EndDate AND [ReceiptTypeId] IN (3,4)) AS [MortgageReceivableAmount], 
								(SELECT SUM([ReceivedAmount]) FROM [ContractReceiptView] WHERE [ContractId] = CR.[ContractId] AND [ScheduledDate] >= @BeginDate AND [ScheduledDate] < @EndDate AND [ReceiptTypeId] IN (1,2)) AS [InstalmentReceivedAmount], 
								(SELECT SUM([ReceivedAmount]) FROM [ContractReceiptView] WHERE [ContractId] = CR.[ContractId] AND [ScheduledDate] >= @BeginDate AND [ScheduledDate] < @EndDate AND [ReceiptTypeId] IN (3,4)) AS [MortgageReceivedAmount], 
								(SELECT SUM([ReceivedAmount]) FROM [ContractReceiptView] WHERE [ContractId] = CR.[ContractId]) AS [ReceivedAmount]
				FROM		[ContractReceiptView] AS CR
				GROUP BY
								CR.[ContractId]
			) AS CR ON C.[Id] = CR.[ContractId]
		WHERE	C.[IsDeleted] = 0 AND C.[SignatureDate] >= @BeginDate AND C.[SignatureDate] < @EndDate
	),
	[CancellationView]
	AS
	(
		SELECT	CL.[Id], 
						CL.[AgreementId]
		FROM		[dbo].[Cancellation] AS CL
			INNER JOIN [AgreementView] AS AG ON CL.[AgreementId] = AG.[Id]
		WHERE	CL.[IsDeleted] = 0 AND CL.[CancellationDate] >= @BeginDate AND CL.[CancellationDate] < @EndDate
	),
	[AgreementDetailView]
	AS
	(
		SELECT	AG.[Id] AS [AgreementId], 
						SO.[Id] AS [SmallOrderId],
						LO.[Id] AS [LargeOrderId],
						C.[Id] AS [ContractId]
		FROM		[AgreementView] AS AG
			LEFT OUTER JOIN [SmallOrderView] AS SO ON AG.[Id] = SO.[AgreementId]
			LEFT OUTER JOIN [LargeOrderView] AS LO ON AG.[Id] = LO.[AgreementId]
			LEFT OUTER JOIN [ContractView] AS C ON AG.[Id] = C.[AgreementId]
	),
	[SmallOrderAllView]
	AS
	(
		SELECT	SO.[Id], 
						SO.[AgreementId]
		FROM		[dbo].[SmallOrder] AS SO
			INNER JOIN [AgreementView] AS AG ON SO.[AgreementId] = AG.[Id]
		WHERE	SO.[IsDeleted] = 0 AND SO.[SignatureDate] < @EndDate
	),
	[LargeOrderAllView]
	AS
	(
		SELECT	LO.[Id], 
						LO.[AgreementId]
		FROM		[dbo].[LargeOrder] AS LO
			INNER JOIN [AgreementView] AS AG ON LO.[AgreementId] = AG.[Id]
		WHERE	LO.[IsDeleted] = 0 AND LO.[SignatureDate] < @EndDate
	),
	[ContractReceiptAllView]
	AS
	(
		SELECT	CR.[Id],
						CR.[ContractId],
						CR.[ReceiptTypeId],
						CR.[ScheduledDate], 
						CR.[ReceivableAmount], 
						ISNULL(CRR.[ReceivedAmount], 0) AS [ReceivedAmount]
		FROM		[dbo].[ContractReceipt] AS CR
			LEFT OUTER JOIN
			(
				SELECT	[ContractReceiptId],
								SUM([ReceivedAmount]) AS [ReceivedAmount]
				FROM		[dbo].[ContractReceivedReceipt]
				WHERE	[IsDeleted] = 0 AND [ReceivedDate] < @EndDate
				GROUP BY
								[ContractReceiptId]
			) AS CRR 	ON CR.[Id] = CRR.[ContractReceiptId]
		WHERE	CR.[IsDeleted] = 0
	),
	[ContractAllView]
	AS
	(
		SELECT	C.[Id], 
						C.[AgreementId],
						AG.[SaleArea], 
						C.[ReducedTotalPrice] AS [TotalAmount],
						CASE WHEN ISNULL(CR.[InstalmentReceivableAmount], 0) < ISNULL(CR.[InstalmentReceivedAmount], 0) THEN 0 ELSE ISNULL(CR.[InstalmentReceivableAmount], 0) - ISNULL(CR.[InstalmentReceivedAmount], 0) END AS [DiffInstalmentReceivableAmount], 
						CASE WHEN ISNULL(CR.[MortgageReceivableAmount], 0) < ISNULL(CR.[MortgageReceivedAmount], 0) THEN 0 ELSE ISNULL(CR.[MortgageReceivableAmount], 0) - ISNULL(CR.[MortgageReceivedAmount], 0) END AS [DiffMortgageReceivedAmount], 
						ISNULL(CR.[ReceivedAmount], 0) AS [ReceivedAmount]
		FROM		[dbo].[Contract] AS C
			INNER JOIN [AgreementView] AS AG ON C.[AgreementId] = AG.[Id]
			LEFT OUTER JOIN
			(
				SELECT	CR.[ContractId],
								(SELECT SUM([ReceivableAmount]) FROM [ContractReceiptAllView] WHERE [ContractId] = CR.[ContractId] AND [ScheduledDate] < @EndDate AND [ReceiptTypeId] IN (1,2)) AS [InstalmentReceivableAmount], 
								(SELECT SUM([ReceivableAmount]) FROM [ContractReceiptAllView] WHERE [ContractId] = CR.[ContractId] AND [ScheduledDate] < @EndDate AND [ReceiptTypeId] IN (3,4)) AS [MortgageReceivableAmount], 
								(SELECT SUM([ReceivedAmount]) FROM [ContractReceiptAllView] WHERE [ContractId] = CR.[ContractId] AND [ScheduledDate] < @EndDate AND [ReceiptTypeId] IN (1,2)) AS [InstalmentReceivedAmount], 
								(SELECT SUM([ReceivedAmount]) FROM [ContractReceiptAllView] WHERE [ContractId] = CR.[ContractId] AND [ScheduledDate] < @EndDate AND [ReceiptTypeId] IN (3,4)) AS [MortgageReceivedAmount], 
								(SELECT SUM([ReceivedAmount]) FROM [ContractReceiptAllView] WHERE [ContractId] = CR.[ContractId]) AS [ReceivedAmount]
				FROM		[ContractReceiptAllView] AS CR
				GROUP BY
								CR.[ContractId]
			) AS CR ON C.[Id] = CR.[ContractId]
		WHERE	C.[IsDeleted] = 0 AND C.[SignatureDate] < @EndDate
	),
	[CancellationAllView]
	AS
	(
		SELECT	CL.[Id], 
						CL.[AgreementId]
		FROM		[dbo].[Cancellation] AS CL
			INNER JOIN [AgreementView] AS AG ON CL.[AgreementId] = AG.[Id]
		WHERE	CL.[IsDeleted] = 0 AND CL.[CancellationDate] < @EndDate
	),
	[AgreementDetailAllView]
	AS
	(
		SELECT	AG.[Id] AS [AgreementId], 
						SO.[Id] AS [SmallOrderId],
						LO.[Id] AS [LargeOrderId],
						C.[Id] AS [ContractId]
		FROM		[AgreementView] AS AG
			LEFT OUTER JOIN [SmallOrderAllView] AS SO ON AG.[Id] = SO.[AgreementId]
			LEFT OUTER JOIN [LargeOrderAllView] AS LO ON AG.[Id] = LO.[AgreementId]
			LEFT OUTER JOIN [ContractAllView] AS C ON AG.[Id] = C.[AgreementId]
	)
	SELECT	(SELECT COUNT(*) FROM [AgreementDetailView] AS AGD INNER JOIN [ActiveAgreementView] AS AG ON AGD.[AgreementId] = AG.[Id] WHERE AGD.[SmallOrderId] IS NOT NULL AND AGD.[LargeOrderId] IS NULL AND AGD.[ContractId] IS NULL) AS [SmallOrderCount],
					(SELECT COUNT(*) FROM [AgreementDetailView] AS AGD INNER JOIN [ActiveAgreementView] AS AG ON AGD.[AgreementId] = AG.[Id] WHERE AGD.[LargeOrderId] IS NOT NULL AND AGD.[ContractId] IS NULL) AS [LargeOrderCount],
					(SELECT COUNT(*) FROM [AgreementDetailView] AS AGD INNER JOIN [ActiveAgreementView] AS AG ON AGD.[AgreementId] = AG.[Id] WHERE AGD.[ContractId] IS NOT NULL) AS [ContractCount],
					(SELECT COUNT(*) FROM [CancellationView] AS CL) AS [CancellationCount],
					(SELECT COUNT(*) FROM [AgreementDetailView] AS AGD INNER JOIN [ActiveAgreementView] AS AG ON AGD.[AgreementId] = AG.[Id] WHERE (AGD.[SmallOrderId] IS NOT NULL OR AGD.[LargeOrderId] IS NOT NULL) AND AGD.[ContractId] IS NULL) AS [WithoutContractCount],

					(SELECT SUM(C.[SaleArea]) FROM [ContractView] AS C INNER JOIN [ActiveAgreementView] AS AG ON C.[AgreementId] = AG.[Id]) AS [SaleArea], 
					(SELECT SUM(C.[TotalAmount]) FROM [ContractView] AS C INNER JOIN [ActiveAgreementView] AS AG ON C.[AgreementId] = AG.[Id]) AS [TotalAmount], 
					(SELECT SUM(C.[DiffInstalmentReceivableAmount]) FROM [ContractView] AS C INNER JOIN [ActiveAgreementView] AS AG ON C.[AgreementId] = AG.[Id]) AS [DiffInstalmentReceivableAmount], 
					(SELECT SUM(C.[DiffMortgageReceivedAmount]) FROM [ContractView] AS C INNER JOIN [ActiveAgreementView] AS AG ON C.[AgreementId] = AG.[Id]) AS [DiffMortgageReceivedAmount], 
					(SELECT SUM(C.[ReceivedAmount]) FROM [ContractView] AS C INNER JOIN [ActiveAgreementView] AS AG ON C.[AgreementId] = AG.[Id]) AS [ReceivedAmount], 

					(SELECT COUNT(*) FROM [AgreementDetailAllView] AS AGD INNER JOIN [ActiveAgreementView] AS AG ON AGD.[AgreementId] = AG.[Id] WHERE AGD.[SmallOrderId] IS NOT NULL AND AGD.[LargeOrderId] IS NULL AND AGD.[ContractId] IS NULL) AS [AllSmallOrderCount],
					(SELECT COUNT(*) FROM [AgreementDetailAllView] AS AGD INNER JOIN [ActiveAgreementView] AS AG ON AGD.[AgreementId] = AG.[Id] WHERE AGD.[LargeOrderId] IS NOT NULL AND AGD.[ContractId] IS NULL) AS [AllLargeOrderCount],
					(SELECT COUNT(*) FROM [AgreementDetailAllView] AS AGD INNER JOIN [ActiveAgreementView] AS AG ON AGD.[AgreementId] = AG.[Id] WHERE AGD.[ContractId] IS NOT NULL) AS [AllContractCount],
					(SELECT COUNT(*) FROM [CancellationAllView] AS CL) AS [AllCancellationCount],
					(SELECT COUNT(*) FROM [AgreementDetailAllView] AS AGD INNER JOIN [ActiveAgreementView] AS AG ON AGD.[AgreementId] = AG.[Id] WHERE (AGD.[SmallOrderId] IS NOT NULL OR AGD.[LargeOrderId] IS NOT NULL) AND AGD.[ContractId] IS NULL) AS [AllWithoutContractCount],

					(SELECT SUM(C.[SaleArea]) FROM [ContractAllView] AS C INNER JOIN [ActiveAgreementView] AS AG ON C.[AgreementId] = AG.[Id]) AS [AllSaleArea], 
					(SELECT SUM(C.[TotalAmount]) FROM [ContractAllView] AS C INNER JOIN [ActiveAgreementView] AS AG ON C.[AgreementId] = AG.[Id]) AS [AllTotalAmount], 
					(SELECT SUM(C.[DiffInstalmentReceivableAmount]) FROM [ContractAllView] AS C INNER JOIN [ActiveAgreementView] AS AG ON C.[AgreementId] = AG.[Id]) AS [AllDiffInstalmentReceivableAmount], 
					(SELECT SUM(C.[DiffMortgageReceivedAmount]) FROM [ContractAllView] AS C INNER JOIN [ActiveAgreementView] AS AG ON C.[AgreementId] = AG.[Id]) AS [AllDiffMortgageReceivedAmount], 
					(SELECT SUM(C.[ReceivedAmount]) FROM [ContractAllView] AS C INNER JOIN [ActiveAgreementView] AS AG ON C.[AgreementId] = AG.[Id]) AS [AllReceivedAmount], 

					CASE WHEN (SELECT COUNT(*) FROM [AgreementView] AS AG) = 0 THEN 0 ELSE (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [CancellationView] AS CL) / (SELECT CAST(COUNT(*) AS numeric(20, 2)) FROM [AgreementView] AS AG) END AS [AllCancellationAgreementRatio]
END
